﻿Imports Microsoft.Office.Interop

Public Class BusyButFine

    Public WhatWork As String = ""
    Public Params As List(Of Object)
    Public ReturnedValues As New List(Of Object)

    Private Sub BGW_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BGW.DoWork

        ReturnedValues.Clear()

        Select Case WhatWork

            Case "CCMFile"
                CCMFile(Params(0), Params(1), Params(2), Params(3), Params(4), Params(5), Params(6), Params(7), Params(8), Params(9))
            Case "CalculateProduct"
                CalculateProduct()
            Case "OpenBusinessCase"
                OpenBusinessCase(Params(0), Params(1))
            Case "Cases"
                Cases(Params(0), Params(1), Params(2))
            Case "LoadAcadDataset"
                LoadAcadDataset(Params(0), Params(1), Params(2))
            Case "LoadAcadDatasetGrid"
                LoadAcadDatasetGrid(Params(0), Params(1))
            Case "UpdateActivePayments"
                UpdateActivePayments(Params(0), Params(1), Params(2))
            Case "UpdateActivePaymentsBudget"
                UpdateActivePaymentsBudget(Params(0), Params(1), Params(2))
            Case "FreezePayments"
                FreezePayments(Params(0), Params(1), Params(2), Params(3))
            Case "FreezePaymentsBudget"
                FreezePaymentsBudget(Params(0), Params(1), Params(2), Params(3))
            Case "UnfreezePayments"
                UnfreezePayments(Params(0), Params(1), Params(2), Params(3))
            Case "ExportToExcel"
                ExportToExcel(Params(0))
            Case "AcadAllocationsView"
                AcadAllocationsView(Params(0), Params(1), Params(2), Params(3))
            Case "ApplyAcademyAllocations"
                ApplyAcademyAllocations(Params(0), Params(1), Params(2))
            Case "UpdateLRAllocations"
                UpdateLRAllocations(Params(0), Params(1), Params(2), Params(3), Params(4), Params(5), Params(6))
            Case "LoadBusyDataView"
                LoadBusyDataView(Params(0), Params(1))
            Case "ExcelPaymentsReport"
                ExcelPaymentsReport(Params(0), Params(1), Params(2), Params(3), Params(4))
            Case "LoadRTFReport"
                LoadRTFReport(Params(0), Params(1), Params(2))
            Case Else
                'do nothing
        End Select

        If BGW.CancellationPending Then
            e.Cancel = True
        End If

    End Sub

    Private Sub LoadBusyDataView(ByVal SQL As String, ByVal Message As String)
        Dim DV As DataView

        SetLabelText_ThreadSafe(MessageLabel, Message)


        DV = LoadDataView(SQL)

        ReturnedValues.Add(DV)

    End Sub

    Private Sub BusyButFine_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

        CurrentProcessLabel.Text = ""

        If WhatWork = "" Then
            DialogResult = Windows.Forms.DialogResult.OK
            Me.Close()

        Else
            BGW.RunWorkerAsync()
        End If


    End Sub

    Private Sub LoadRTFReport(ByVal FieldsView As DataView, ByVal TheRecord As DataRowView, ByVal RTF As String)

        SetLabelText_ThreadSafe(MessageLabel, "Loading report, please wait...")

        Dim r As DataRowView
        Dim ZeroText As String
        Dim TheValue As String
        Dim DP As Integer
        Dim TheField As String

        For Each r In FieldsView

            TheField = "[" & r.Item("Field_Code") & "]"

            While RTF.Contains(TheField)

                ZeroText = r.Item("Zero_Text")


                If IsDBNull(TheRecord(r.Item("Field_Code"))) Then
                    TheValue = "0"
                Else
                    TheValue = TheRecord(r.Item("Field_Code")).ToString
                End If

                Try
                    If TheValue = 0 Then
                        TheValue = ZeroText
                    End If
                Catch ex As Exception
                    'keep the value as it is
                End Try

                DP = r.Item("Decimals")
                If DP = -1 Then

                    RTF = RTF.Replace(TheField, TheValue)

                Else
                    Try
                        RTF = RTF.Replace(TheField, FormatNumber(TheValue, DP, TriState.True, TriState.False, TriState.True))

                    Catch ex As Exception

                        RTF = RTF.Replace(TheField, TheValue)

                    End Try
                End If


            End While

        Next

        RTF = CalculateExpresions(RTF)

        ReturnedValues.Add(RTF)


    End Sub

    Public Function CalculateExpresions(ByVal RTF As String) As String

        Dim a As Integer
        Dim TheString As String
        Dim TheCalc As String
        Dim TheAnswer As String = "#Error#"
        Dim LastCheck As Integer
        Dim startpoint As Integer
        Dim Answerconvert As Double

        startpoint = 0

        Dim RichTextBoxEx1 As New RichTextBoxEx

        RichTextBoxEx1.Rtf = RTF

        'find each { and ending with }
        a = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
        LastCheck = a

        While a <> -1
            'find the end position }
            If RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase) <> -1 Then
                If a >= startpoint Then
                    TheString = Mid(RichTextBoxEx1.Text, 1, RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase))

                    TheString = Mid(TheString, a + 2, Len(TheString))

                    TheCalc = TheString

                    TheCalc = TheCalc.Replace("{", "")
                    TheCalc = TheCalc.Replace("}", "")

                    If TheCalc <> "DTS" Then
                        If TheCalc <> "DTE" Then


                            RichTextBoxEx1.SelectionStart = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
                            RichTextBoxEx1.SelectionLength = RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase) - RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase) + 1

                            RichTextBoxEx1.Find("{" & TheString & "}")
                            Try
                                If TheCalc.ToUpper.Contains("WHEN") Then
                                    Dim thea As DataTable

                                    TheCalc = TheCalc.Replace("&lt;", "<")
                                    TheCalc = TheCalc.Replace("&gt;", ">")

                                    thea = LoadDataTable("select CASE " & TheCalc & " END as Value")
                                    TheAnswer = thea(0).Item("Value")
                                    'try converting it to a value first if not then it is text
                                    Try
                                        Answerconvert = TheAnswer
                                        RichTextBoxEx1.SelectedText = Format(Answerconvert, "#,0.###########")
                                    Catch ex As Exception
                                        RichTextBoxEx1.SelectedText = TheAnswer
                                    End Try
                                Else
                                    TheAnswer = EvaluateExpression(TheCalc)
                                    Answerconvert = TheAnswer
                                    RichTextBoxEx1.SelectedText = Format(Answerconvert, "#,0.###########")
                                End If

                            Catch ex As Exception
                                RichTextBoxEx1.SelectedText = "#Error#"
                            End Try



                        End If
                    End If

                End If
            End If

            a = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
            If LastCheck = a Then
                a = -1
            Else
                LastCheck = a
            End If
        End While


        CalculateExpresions = RichTextBoxEx1.Rtf

    End Function

    Private Sub ApplyAcademyAllocations(ByVal Period_ID As Integer, ByVal Template_ID As Integer, ByVal RIDList As List(Of String))

        Dim RID As String

        SetLabelText_ThreadSafe(MessageLabel, "Updating allocations, please wait...")

        Dim i As Integer

        i = 0

        For Each RID In RIDList

            If BGW.CancellationPending Then
                Exit Sub
            End If

            i = i + 1

            SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RIDList.Count.ToString)

            RunNonQuery("exec dbo.spAcad_Apply_Allocations @UserPeriod=" & Period_ID & ", @Template_ID=" & Template_ID & ", @RID='" & RID & "', @User_Name='" & CurrentUser.Name & "'")
        Next



    End Sub

    Private Sub AcadAllocationsView(ByVal Period_ID As Integer, ByVal TheType As Integer, ByVal UPINList As List(Of String), ByVal ShowDiff As Integer)

        SetLabelText_ThreadSafe(MessageLabel, "Loading allocation data, please wait...")

        Dim DV As DataView

        Dim RIDstr As String = ""
        Dim RID As DataRowView

        Dim UPINstr As String = ""
        Dim UPIN As String = ""

        If UPINList.Count <> 0 Then
            For Each UPIN In UPINList
                If UPINstr = "" Then
                    UPINstr = "'" & UPIN & "'"
                Else
                    UPINstr = UPINstr & ", '" & UPIN & "'"
                End If
            Next

            DV = LoadDataView("Select RID from All_provider_Fields where UPIN in (" & UPINstr & ") and Lead = 1")

            For Each RID In DV
                If RIDstr = "" Then
                    RIDstr = "''" & RID("RID") & "''"
                Else
                    RIDstr = RIDstr & ", ''" & RID("RID") & "''"
                End If
            Next
        Else
            RIDstr = ""
        End If


        Dim Sql As String

        Sql = "exec dbo.spAcad_Allocations_Changes_View " & Period_ID & ", " & TheType & ", '" & RIDstr & "', " & ShowDiff

        DV = LoadDataView(Sql)

        ReturnedValues.Add(DV)
    End Sub


    Private Sub FreezePaymentsBudget(ByVal Period_ID As Integer, ByVal Budget_ID As String, ByVal RidList As List(Of String), ByVal Month As Date)

        Dim rid As String
        Dim i As Integer = 0


        Dim sql As String
        Dim Allocation_ID As String
        Dim DV As DataView
        Dim rec As DataRowView

        SetLabelText_ThreadSafe(MessageLabel, "Freezing Payments.  Depending on the amount of providers to be updated, this could take a while...")

        sql = "select Allocation_ID from Allocation_Lines where Budget_ID = '" & Budget_ID & "'"
        DV = LoadDataView(sql)

        If DV.Count <> 0 Then

            For Each rid In RidList

                i = i + 1

                SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RidList.Count.ToString)

                For Each rec In DV
                    Allocation_ID = rec("Allocation_ID")
                    RunNonQuery("exec dbo.spBudget_Freeze_Payments " & Period_ID & ", '" & Allocation_ID & "', '" & rid & "', '" & Format(Month, "dd MMM yyyy") & "'")
                Next
            Next
        End If

    End Sub

    Private Sub FreezePayments(ByVal Period_ID As Integer, ByVal Allocation_ID As String, ByVal RidList As List(Of String), ByVal Month As Date)

        Dim rid As String
        Dim i As Integer = 0


        SetLabelText_ThreadSafe(MessageLabel, "Freezing Payments.  Depending on the amount of providers to be updated, this could take a while...")

        For Each rid In RidList
            i = i + 1

            SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RidList.Count.ToString)

            RunNonQuery("exec dbo.spBudget_Freeze_Payments " & Period_ID & ", '" & Allocation_ID & "', '" & rid & "', '" & Format(Month, "dd MMM yyyy") & "'")


        Next


    End Sub

    Private Sub UnfreezePayments(ByVal Period_ID As Integer, ByVal Allocation_ID As String, ByVal RidList As List(Of String), ByVal Month As Date)

        Dim rid As String
        Dim i As Integer = 0


        SetLabelText_ThreadSafe(MessageLabel, "Unfreezing Payments.  Depending on the amount of providers to be updated, this could take a while...")

        For Each rid In RidList
            i = i + 1

            SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RidList.Count.ToString)

            RunNonQuery("exec dbo.spBudget_Unfreeze_Payments " & Period_ID & ", '" & Allocation_ID & "', '" & rid & "', '" & Format(Month, "dd MMM yyyy") & "'")


        Next


    End Sub

    Private Sub UpdateActivePayments(ByVal Period_ID As Integer, ByVal Allocation_ID As String, ByVal RidList As List(Of String))

        Dim rid As String
        Dim i As Integer = 0


        SetLabelText_ThreadSafe(MessageLabel, "Updating payments.  Depending on the amount of providers to be updated, this could take a while...")

        For Each rid In RidList
            i = i + 1

            SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RidList.Count.ToString)

            RunNonQuery("exec dbo.spBudget_Update_Active_Payments " & Period_ID & ", '" & Allocation_ID & "', '" & rid & "'")


        Next

    End Sub


    Private Sub UpdateActivePaymentsBudget(ByVal Period_ID As String, ByVal Budget_ID As String, ByVal RidList As List(Of String))

        Dim rid As String
        Dim i As Integer = 0

        SetLabelText_ThreadSafe(MessageLabel, "Updating payments.  Depending on the amount of providers to be updated, this could take a while...")

        Dim sql As String
        Dim Allocation_ID As String
        Dim DV As DataView
        Dim rec As DataRowView

        sql = "select Allocation_ID from Allocation_Lines where Budget_ID = '" & Budget_ID & "'"
        DV = LoadDataView(sql)

        If DV.Count <> 0 Then

            For Each rid In RidList

                i = i + 1
                SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & RidList.Count.ToString)

                For Each rec In DV

                    Allocation_ID = rec("Allocation_ID")
                    RunNonQuery("exec dbo.spBudget_Update_Active_Payments " & Period_ID & ", '" & Allocation_ID & "', '" & rid & "'")


                Next

            Next

        End If


    End Sub

    Private Sub Cases(ByVal Datacut_ID As String, ByVal Period_ID As Integer, ByVal RID As String)

        SetLabelText_ThreadSafe(MessageLabel, "Building the business case, please wait...")

        If BGW.CancellationPending Then
            Exit Sub
        End If

        Dim CaseData As DataView

        CaseData = LoadDataView("exec spCases_Results @DataCut_ID='" & Datacut_ID & "', @Period_ID = " & Period_ID & ", @RID = '" & RID & "', @User_Name = '" & CurrentUser.Name & "'")

        If BGW.CancellationPending Then
            Exit Sub
        End If

        ReturnedValues.Add(CaseData)

    End Sub

    Private Sub CCMFile(ByVal Period_ID As Integer, ByVal ProviderType As String, ByVal TerritoryCode As String, ByVal RegionCode As String, ByVal LACode As String, ByVal UPIN As String, ByVal Budget_ID As String, ByVal DeliverableCode As Integer, ByVal FundingStream As String, ByVal Path As String)
        Dim CCMData As DataSet
        Dim Headers As DataView
        Dim Deliverables As DataView
        Dim Budgets As DataView

        Dim rs As DataRowView
        Dim rb As DataRowView

        Dim FilesList As DataView

        Dim BudgetString As String = ""


        SetLabelText_ThreadSafe(MessageLabel, "Running the CCM File.  Depending on the size of the selection you made this could take a while.")

        If FundingStream = "" Then

            'Dim sql As String

            'sql = "select distinct Budget_ID from Allocation_Lines where Allocation_ID in ( " &
            '        "select distinct Allocation_ID from Allocations where Period_ID = " & Period_ID & " and Status = 'Approved' " &
            '        "and RID in (select RID from All_Provider_Fields where Lead = 1  "

            'If RegionCode <> "" Then
            '    sql = sql & "and Region_Code = '" & RegionCode & "' "
            'End If

            ''If ProviderType <> "" Then
            ''    sql = sql & "and Provider_Type_Code = '" & ProviderType & "' "
            ''Else
            ''    sql = sql & "and Provider_Type_Code in (select Provider_Type_Code  from Provider_Types where Provider_Type_Code <> 'Acade') "
            ''End If


            'If UPIN <> "" Then
            '    sql = sql & "and UPIN in (" & UPIN & ") "
            'End If

            'sql = sql & "))"

            FilesList = LoadDataView("select * from CCM_Funding_Streams")
        Else
            FilesList = LoadDataView("select * from CCM_Funding_Streams where CCM_Funding_Stream = '" & FundingStream & "'")
        End If


        For Each rs In FilesList

            'Budgets = LoadDataView("select * from Budgets where Budget_ID in (select Budget_ID from CCM_Filenames where Filename = '" & rs("Filename") & "')")

            BudgetString = ""

            'For Each rb In Budgets
            '    If BudgetString = "" Then
            '        BudgetString = "''" & rb("Budget_ID") & "''"
            '    Else
            '        BudgetString = BudgetString & ", ''" & rb("Budget_ID") & "''"
            '    End If

            'Next

            If rs("FileName") = "AYF" Then
                ProviderType = "Acade"
            Else
                ProviderType = ""
            End If

            If BGW.CancellationPending Then
                Exit Sub
            End If

            'A file
            SetLabelText_ThreadSafe(CurrentProcessLabel, "Loading the " & rs("Description") & " data for the A file...")

            CCMData = LoadDataSet("exec dbo.spCCM_Output_File @FileType = 'A', @Period_ID = " & Period_ID & ", @ProviderType = '" & ProviderType & "', @TerritoryCode = '" & TerritoryCode & "', @RegionCode = '" & RegionCode & "', @LACode = '" & LACode & "',@UPIN = '" & UPIN & "', @Budget_ID = '" & BudgetString & "', @Dataset = 0, @DeliverableCode = " & DeliverableCode & ", @FundingStream = '" & rs("CCM_Funding_Stream") & "'")

            Headers = CCMData.Tables(0).DefaultView

            Deliverables = CCMData.Tables(1).DefaultView

            If BGW.CancellationPending Then
                Exit Sub
            End If

            'load into excel
            SetLabelText_ThreadSafe(CurrentProcessLabel, "Generating the " & rs("Description") & " excel A file...")
            WriteCCMFiletoExcel(Headers, Deliverables, "A", Path, rs("FileName"), Period_ID)

            If BGW.CancellationPending Then
                Exit Sub
            End If

            'B File
            SetLabelText_ThreadSafe(CurrentProcessLabel, "Loading the " & rs("Description") & " data for the B file...")

            CCMData = LoadDataSet("exec dbo.spCCM_Output_File @FileType = 'B', @Period_ID = " & Period_ID & ", @ProviderType = '" & ProviderType & "', @TerritoryCode = '" & TerritoryCode & "', @RegionCode = '" & RegionCode & "', @LACode = '" & LACode & "',@UPIN = '" & UPIN & "', @Budget_ID = '" & BudgetString & "', @Dataset = 0, @DeliverableCode = " & DeliverableCode & ", @FundingStream = '" & rs("CCM_Funding_Stream") & "'")

            Headers = CCMData.Tables(0).DefaultView

            Deliverables = CCMData.Tables(1).DefaultView

            If BGW.CancellationPending Then
                Exit Sub
            End If

            'load into excel
            SetLabelText_ThreadSafe(CurrentProcessLabel, "Generating the " & rs("Description") & " excel B file...")
            WriteCCMFiletoExcel(Headers, Deliverables, "B", Path, rs("FileName"), Period_ID)
        Next

    End Sub


    Private Sub WriteCCMFiletoExcel(ByVal HeaderView As DataView, ByVal DeliView As DataView, ByVal FileType As String, ByVal Path As String, ByVal FundFileName As String, ByVal Period_ID As Integer)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        xlApp = New Excel.ApplicationClass
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlRng As Excel.Range

        Dim r As Integer

        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkSheet = xlWorkBook.Sheets("sheet3")
        xlWorkSheet.Name = "Regional_Breakdown"

        '--Pointless Region breakdown Start
        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Number"
        xlWorkSheet.Cells(r, 5) = "Contract Type"
        xlWorkSheet.Cells(r, 6) = "Deliverable Name"
        xlWorkSheet.Cells(r, 7) = "Region"
        xlWorkSheet.Cells(r, 8) = "Value"
        xlWorkSheet.Cells(r, 9) = "Deliverable Code"
        xlWorkSheet.Cells(r, 10) = "Planning Unit"
        '--Pointless Region breakdown End


        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = "Contract_Header"

        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Type"
        xlWorkSheet.Cells(r, 5) = "Programme"
        xlWorkSheet.Cells(r, 6) = "Start Date"
        xlWorkSheet.Cells(r, 7) = "End Date"
        xlWorkSheet.Cells(r, 8) = "Template"
        xlWorkSheet.Cells(r, 9) = "Contract Advisor"
        xlWorkSheet.Cells(r, 10) = "Parent Contract"
        xlWorkSheet.Cells(r, 11) = "Unique Academy Identifier"
        xlWorkSheet.Cells(r, 12) = "Sponsor/Trust"

        'now do the records
        xlRng = xlWorkSheet.Range("A4:L" & HeaderView.Count + r)

        Dim TheValues(HeaderView.Count - 1, 11) As Object

        r = -1

        Dim Rec As DataRowView

        If HeaderView.Count <> 0 Then
            For Each Rec In HeaderView
                r = r + 1
                For c = 0 To HeaderView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValues(r, c) = Rec.Item(c)
                Next
            Next

            xlRng.Value = TheValues
        End If


        xlWorkSheet = xlWorkBook.Sheets("sheet2")
        xlWorkSheet.Name = "Deliverables"

        r = 3
        xlWorkSheet.Cells(r, 1) = "Org Unit"
        xlWorkSheet.Cells(r, 2) = "UKPRN"
        xlWorkSheet.Cells(r, 3) = "UPIN"
        xlWorkSheet.Cells(r, 4) = "Contract Number"
        xlWorkSheet.Cells(r, 5) = "Contract Type"
        xlWorkSheet.Cells(r, 6) = "Deliverable Name"
        xlWorkSheet.Cells(r, 7) = "Period"
        xlWorkSheet.Cells(r, 8) = "Year"
        xlWorkSheet.Cells(r, 9) = "Value"
        xlWorkSheet.Cells(r, 10) = "Deliverable Code"
        xlWorkSheet.Cells(r, 11) = "Planning Unit"
        xlWorkSheet.Cells(r, 12) = "Planning Unit Frequency"

        'now do the records
        xlRng = xlWorkSheet.Range("A4:L" & DeliView.Count + r)

        Dim TheValuesDel(DeliView.Count - 1, 11) As Object

        r = -1

        If DeliView.Count <> 0 Then
            For Each Rec In DeliView
                r = r + 1

                For c = 0 To DeliView.Table.Columns.Count - 1 'ignore the first column, because it is not used in the file
                    TheValuesDel(r, c) = Rec.Item(c)
                Next
            Next

            xlRng.Value = TheValuesDel
        End If

        Dim FileName As String

        FileName = "EFA "


        FileName = FileName & FundFileName


        FileName = FileName & Mid(Period_ID.ToString, 3, 4) & " "


        FileName = FileName & Format(Now, "ddMMyyyyHHmmss") & " "
        FileName = FileName & FileType

        If DeliView.Count <> 0 Then
            xlWorkBook.SaveAs(Path & FileName)
        End If

        xlWorkBook.Close(False)

        xlApp.Quit()

        Try
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub CalculateProduct()
        Dim ds As DataView

        SetLabelText_ThreadSafe(MessageLabel, "Calculating your product.  Depending on the size of the selection you made this could take a while...")

        ds = LoadDataView("exec dbo.spCalculateProduct @scenario='08031105374675', @product=98, @username='csmith2', @RID=''")

    End Sub

    Private Sub OpenBusinessCase(ByVal RID As String, ByVal BCID As String)

    End Sub

    Private Sub BGW_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BGW.RunWorkerCompleted
        If e.Cancelled Then
            DialogResult = Windows.Forms.DialogResult.Cancel

        Else
            DialogResult = Windows.Forms.DialogResult.OK

        End If

        Me.Close()

    End Sub


    Private Sub CancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelBut.Click
        If MessageBox.Show("Are you sure you want to cancel the process?", "Cancel?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            SetLabelText_ThreadSafe(MessageLabel, "Cancelling process, please wait...")

            If BGW.IsBusy Then
                BGW.CancelAsync()
            Else
                DialogResult = Windows.Forms.DialogResult.Cancel
                Me.Close()
            End If
        End If
    End Sub

    Private Sub LoadAcadDataset(ByVal RID As String, ByVal Period_ID As Integer, ByVal Dataset_Code As Integer)

        SetLabelText_ThreadSafe(MessageLabel, "Loading the dataset, please wait...")

        Dim TheData As DataView

        Dim sql As String

        sql = "exec dbo.spAcademy_Datasets @dataset=" & Dataset_Code & ", @userperiod=" & Period_ID & ", @rid='''" & RID & "''', @ShowFieldCode=1"

        TheData = LoadDataView(sql)

        ReturnedValues.Add(TheData)

    End Sub


    Private Sub LoadAcadDatasetGrid(ByVal Period_ID As Integer, ByVal Dataset_Code As Integer)

        SetLabelText_ThreadSafe(MessageLabel, "Loading the dataset, please wait...")

        Dim TheData As DataView

        Dim sql As String


        sql = "exec dbo.spAcademy_Datasets @dataset=" & Dataset_Code & ", @userperiod=" & Period_ID & ", @rid='''''', @ShowFieldCode=0"

        TheData = LoadDataView(sql)



        ReturnedValues.Add(TheData)

    End Sub

    'The Labels
    ' The delegate
    Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String)

    ' The delegates subroutine.
    Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String)
        ' InvokeRequired required compares the thread ID of the calling thread to the thread ID of the creating thread.
        ' If these threads are different, it returns true.
        If [Label].InvokeRequired Then
            Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe)
            Me.Invoke(MyDelegate, New Object() {[Label], [text]})
        Else
            [Label].Text = [text]
        End If
    End Sub

    Private Function GetExcelColumnName(ByVal columnNumber As Integer) As String
        Dim dividend As Integer = columnNumber
        Dim columnName As String = [String].Empty
        Dim modulo As Integer
        While dividend > 0
            modulo = (dividend - 1) Mod 26
            columnName = Convert.ToChar(65 + modulo).ToString() & columnName
            dividend = CInt((dividend - modulo) \ 26)
        End While
        Return columnName
    End Function


    Private Sub ExportToExcel(ByVal TheView As DataView)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        SetLabelText_ThreadSafe(MessageLabel, "Exporting data to excel, please wait...")

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        Dim rc As Integer
        Dim cc As Integer
        Dim c As DataColumn
        Dim r As DataRowView

        j = 0

        'headers
        For Each c In TheView.Table.Columns
            j = j + 1
            xlWorkSheet.Cells(1, j) = c.ColumnName.ToString

        Next

        Dim xlRng As Excel.Range
        xlRng = xlWorkSheet.Range("A2:" & GetExcelColumnName(TheView.Table.Columns.Count) & TheView.Count + 1)

        Dim TheValues(TheView.Count - 1, TheView.Table.Columns.Count - 1) As Object

        rc = -1
        For Each r In TheView
            rc = rc + 1
            cc = -1
            For Each c In TheView.Table.Columns
                cc = cc + 1
                TheValues(rc, cc) = r(c.ColumnName)

                If BGW.CancellationPending Then
                    releaseObject(xlApp)
                    Exit Sub
                End If
            Next
        Next


        xlRng.Value = TheValues



        xlApp.Visible = True


    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub UpdateLRAllocations(ByVal TheMainSQL As String, ByVal CaseFactor_DataCut As String, ByVal Factor_DataCut As String, ByVal Period_ID As Integer, ByVal Allocation_ID As String, ByVal Rate As Double, ByVal TheNotes As String)





        'Dim rec As DataRowView
        'Dim ProviderFactor As String
        'Dim TheFactor As Decimal
        Dim SQL As String
        'Dim Factor As DataView
        'Dim ACheck As DataView
        'Dim FinalResult As Decimal = 0
        'Dim Learners As Integer = 0
        'Dim CurrentAllocation As Decimal
        'Dim CurrentLearners As Integer
        'Dim CurrentFactor As Decimal
        'Dim CurrentRate As Decimal

        Dim TheData As DataView

        SQL = "exec dbo.spLR_Apply_Allocations @TheMainSQL='" & TheMainSQL.Replace("'", "''") & "', @CaseFactor_DataCut='" & CaseFactor_DataCut & "', @Factor_DataCut='" & Factor_DataCut & "', @Period_ID=" & Period_ID & ", @Allocation_ID='" & Allocation_ID & "', @Rate=" & Rate & ", @TheNotes='" & TheNotes & "', @User_Name='" & CurrentUser.Name & "'"

        SetLabelText_ThreadSafe(MessageLabel, "Updating allocations, please wait...")

        RunNonQuery(SQL)

        'SetLabelText_ThreadSafe(MessageLabel, "Getting ready to update allocations, please wait...")

        'TheData = LoadDataView(TheMainSQL)

        'SetLabelText_ThreadSafe(MessageLabel, "Updating allocations, please wait...")

        'If TheData.Count <> 0 Then

        '    Dim i As Integer = 0


        '    For Each rec In TheData
        '        i = i + 1
        '        SetLabelText_ThreadSafe(CurrentProcessLabel, "Updating " & i.ToString & " of " & TheData.Count)
        '        'get the provider factor
        '        If CaseFactor_DataCut = "none" Then
        '            SQL = "select RID, coalesce(Value,0) as Value from dbo.Modelling_Factors " & _
        '                "where datacut_id = '" & Factor_DataCut & "' " & _
        '                "and RID = '" & rec.Item("RID").ToString & "' and Status = 'Approved'"
        '        Else
        '            SQL = "select act.RID, coalesce(coalesce(c.Value,act.Value),0) as Value from " & _
        '            "(select RID, Value from dbo.Modelling_Factors " & _
        '            "where datacut_id = '" & Factor_DataCut & "' and Status = 'Approved' and RID = '" & rec.Item("RID").ToString & "') act " & _
        '            "left outer join " & _
        '            "(select RID, Value from dbo.Modelling_Factors " & _
        '            "where datacut_id = '" & CaseFactor_DataCut & "' and Status = 'Approved' and RID = '" & rec.Item("RID").ToString & "') c on (act.RID = c.RID) "
        '        End If

        '        Factor = LoadDataView(SQL)

        '        If Factor.Count = 0 Then
        '            ProviderFactor = "0"
        '            TheFactor = 0
        '        Else
        '            ProviderFactor = Factor(0).Item("value").ToString
        '            TheFactor = Factor(0).Item("value")
        '        End If

        '        'check to see if the allocations is different from what it is curretly in the allocation table
        '        FinalResult = Math.Round(IIf(IsDBNull(rec.Item("Final Result")), 0, rec.Item("Final Result")), 0)
        '        Learners = Math.Round(IIf(IsDBNull(rec.Item("Learners")), 0, rec.Item("Learners")), 0)

        '        SQL = "select Allocation, Learner_Numbers, Provider_Factor, Rate from allocations where Status = 'Edit' and Period_ID = " & Period_ID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "'"
        '        ACheck = LoadDataView(SQL)

        '        If ACheck.Count <> 0 Then
        '            CurrentAllocation = ACheck(0).Item("Allocation")
        '            CurrentLearners = ACheck(0).Item("Learner_Numbers")
        '            CurrentFactor = ACheck(0).Item("Provider_Factor")
        '            CurrentRate = ACheck(0).Item("Rate")
        '        Else
        '            CurrentAllocation = 0
        '            CurrentLearners = 0
        '            CurrentFactor = 0
        '            CurrentRate = 0
        '        End If

        '        If Math.Round(FinalResult) <> Math.Round(CurrentAllocation) Or Learners <> CurrentLearners Or Math.Round(CurrentFactor, 3) <> Math.Round(TheFactor, 3) Or CurrentRate <> Rate Then
        '            'Set all allocations for this allocation_id and RID to History
        '            SQL = "update Allocations" & _
        '                 " set Status = 'History' " & _
        '                 " where Period_ID = " & Period_ID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "'"
        '            RunNonQuery(SQL)

        '            SQL = "insert into Allocations values (" & _
        '                   Period_ID & ", '" & rec.Item("RID").ToString & "', '" & Allocation_ID & "', '" & Format(Now(), "dd MMM yyyy") & "', '" & CurrentUser.Name & "', " & _
        '                   FinalResult & ", " & Rate.ToString & ", " & ProviderFactor & ", " & Learners & ", 'Approved', '" & TheNotes & "')"
        '            RunNonQuery(SQL)


        '        Else
        '            'set it back to approved, because it has not changed
        '            SQL = "update Allocations" & _
        '                 " set Status = 'Approved' " & _
        '                 " where Period_ID = " & Period_ID & " and Allocation_ID = '" & Allocation_ID & "' and RID = '" & rec.Item("RID").ToString & "' and Status = 'Edit'"
        '            RunNonQuery(SQL)
        '        End If

        '    Next

        'End If



        ''Now set all the allocations that are left in Edit to History
        'SQL = "update Allocations set Status = 'History' where Status = 'Edit' and Allocation_ID = '" & Allocation_ID & "' and Period_ID = " & Period_ID

        'RunNonQuery(SQL)

        SQL = "select distinct RID from allocations where Allocation_ID = '" & Allocation_ID & "' and Period_ID = " & Period_ID

        TheData = LoadDataView(SQL)

        Dim RIDList As New List(Of String)
        RIDList.Clear()

        For Each rec In TheData
            RIDList.Add(rec("RID"))
        Next

        UpdateActivePayments(Period_ID, Allocation_ID, RIDList)

    End Sub

    Private Sub ExcelPaymentsReport(ByVal Fromdate As String, ByVal ToDate As String, ByVal RIDstring As String, ByVal Allocstring As String, ByVal showtots As Integer)

        Dim sql As String
        Dim dv As DataView

        SetLabelText_ThreadSafe(MessageLabel, "Retrieving data, please wait...")

        sql = "exec dbo.spReport_Payment_Schedule @StartDate='" & Fromdate & "', @EndDate='" & ToDate & "', @Territory='''''', @LA='''''', @RID='" & RIDstring & "', @Budget='''''', @Allocation='" & Allocstring & "', @ProvType='''''', @ProviderTotals=" & showtots

        dv = LoadDataView(sql)

        'DoSomeWork("ExportToExcel", p)

        ExportToExcel(dv)

    End Sub


End Class